/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Dictionary;
import java.util.HashSet;
import java.util.Hashtable;
import java.util.Random;
import java.util.Set;
/**
 *
 * @author David
 */
public class Fabricator {
    Random rand;
    int pid, mid;
    Set usedNames, tuples, parentCompanies, childCompanies;
    ArrayList<String> studios;
    Dictionary<Integer,Integer> media;
    String awards[]={"Best Picture", "Best Acting", "Best Writing", "Best Directing"};
    String sql;

    public Fabricator(){
        rand = new Random();
        usedNames = new HashSet();
        tuples = new HashSet();
        childCompanies = new HashSet();
        parentCompanies = new HashSet();
        studios = new ArrayList<String>();
        media = new Hashtable<Integer,Integer>();
        pid=0;
        mid=0;
        sql="";
    }

    public static void main(String args[]) throws IOException{
        Fabricator f = new Fabricator();
        f.run();
    }

    public void run() throws IOException{
        sql+="use MMDB;\n";
        int numMovies=10;
        int iter=100;
        for(int i=0; i<iter; i++){
            person();
        }
        for(int i=0; i<iter; i++){
            studio();
        }
        award();
        for(int i=0; i<numMovies; i++){
            movie();
        }
        for(int i=0; i<iter; i++){
            tvshow();
        }
        for(int i=0; i<iter; i++){
            radioshow();
        }
        for(int i=0; i<iter/10; i++){
            ownedBy();
        }
        for(int i=0; i<iter; i++){
            financed();
        }
        for(int i=0; i<iter; i++){
            won(numMovies);
        }
        FileWriter fstream = new FileWriter("populate.sql");
        BufferedWriter out = new BufferedWriter(fstream);
        out.write(sql);
        out.close();
        System.out.println(sql);
    }

    public String person(){
        String s="INSERT INTO PERSON\n";
        s+="(PID, Name, YrBorn, YrDied)\n";
        s+="values\n";
        int birth = getYear();
        String death = getBoolean(25)?""+getYear(birth):"NULL";
        s+="("+(pid++)+",'"+getUniqueString()+"',"+birth+","+death+");\n";
        sql+=s;
        return s;
    }

    public String studio(){
        String s="INSERT INTO STUDIO\n";
        s+="(Name, YrFounded)\n";
        s+="values\n";
        int yrFounded = rand.nextInt(20)+1880;
        String name = getUniqueString();
        studios.add(name);
        s+="('"+name+"',"+yrFounded+");\n";
        sql+=s;
        return s;
    }

    public String movie(){
        String s = "INSERT INTO MEDIA\n";
        s+="(MedID, Title, YrMade, Lang, Type)\n";
        s+="values\n";
        Integer year = new Integer(getYear(1920));
        media.put(new Integer(mid), year);
        s+="("+(mid++)+",'"+getUniqueString()+"',"+year+",'"+getLang()+"','movie');\n";
        s+= "INSERT INTO MOVIE\n";
        s+="(MedID, Profits)\n";
        s+="values\n";
        s+="("+(mid-1)+","+rand.nextInt(1000000000)+");\n";
        sql+=s;
        dwp("directed",mid-1);
        dwp("wrote",mid-1);
        dwp("produced",mid-1);
        playedCharacter(mid-1);
        return s;
    }

    public String tvshow(){
        String s = "INSERT INTO MEDIA\n";
        s+="(MedID, Title, YrMade, Lang, Type)\n";
        s+="values\n";
        int yrMade = getYear(1920);
        int yrEnded = getYear(yrMade);
        int noSeasons = rand.nextInt(yrEnded-yrMade)+1;
        s+="("+(mid++)+",'"+getUniqueString()+"',"+yrMade+",'"+getLang()+"','tvshow');\n";
        s+= "INSERT INTO TVSHOW\n";
        s+="(MedID, YrEnded, NoSeasons, Network)\n";
        s+="values\n";
        s+="("+(mid-1)+","+yrEnded+","+noSeasons+",'"+getNetwork()+"');\n";
        sql+=s;
        dwp("directed",mid-1);
        dwp("wrote",mid-1);
        dwp("produced",mid-1);
        playedCharacter(mid-1);
        return s;
    }

    public String radioshow(){
        String s = "INSERT INTO MEDIA\n";
        s+="(MedID, Title, YrMade, Lang, Type)\n";
        s+="values\n";
        int yrMade = getYear(1920);
        int yrEnded = getYear(yrMade);
        s+="("+(mid++)+",'"+getUniqueString()+"',"+yrMade+",'"+getLang()+"','radioshow');\n";
        s+= "INSERT INTO RADIOSHOW\n";
        s+="(MedID, YrEnded, Station)\n";
        s+="values\n";
        s+="("+(mid-1)+","+yrEnded+",'"+getStation()+"');\n";
        sql+=s;
        dwp("directed",mid-1);
        dwp("wrote",mid-1);
        dwp("produced",mid-1);
        playedCharacter(mid-1);
        return s;
    }

    public String dwp(String type){
        return dwp(type, rand.nextInt(mid));
    }

    public String dwp(String type, int m){
        //type = directed, wrote, produced
        String s="INSERT INTO "+type.toUpperCase()+"\n";
        s+="(PID,MedID)\n";
        s+="values\n";
        int p=rand.nextInt(pid);
        while(tuples.contains(type.toLowerCase()+"("+p+","+m+")")){
            p=rand.nextInt(pid);
        }
        tuples.add(type.toLowerCase()+"("+p+","+m+")");
        s+="("+p+","+m+");\n";
        sql+=s;
        return s;
    }

    public String ownedBy(){
        String s="INSERT INTO OWNEDBY\n";
        s+="(ParentCompany,Subsidiary)\n";
        s+="values\n";
        String p=studios.get(rand.nextInt(studios.size()));
        String c=studios.get(rand.nextInt(studios.size()));
        while(childCompanies.contains(c) || parentCompanies.contains(p) || p.equals(c) || tuples.contains("ownedBy("+p+","+c+")")){
            p=studios.get(rand.nextInt(studios.size()));
            c=studios.get(rand.nextInt(studios.size()));
        }
        tuples.add("ownedBy("+p+","+c+")");
        s+="('"+p+"','"+c+"');\n";
        sql+=s;
        return s;
    }

    public String financed(){
        String s = "INSERT INTO FINANCED\n";
        s+="(StudioName,MedID,Cost)\n";
        s+="values\n";
        String studio = studios.get(rand.nextInt(studios.size()));
        int m=rand.nextInt(mid);
        String tuple="financed("+studio+","+m+")";
        while(tuples.contains(tuple)){
            studio = studios.get(rand.nextInt(studios.size()));
            m=rand.nextInt(mid);
            tuple="financed("+studio+","+m+")";            
        }
        tuples.add(tuple);
        s+="('"+studio+"',"+m+","+rand.nextInt(1000000000)+");\n";
        
        sql+=s;
        return s;
    }

    public String playedCharacter(){
        return playedCharacter(rand.nextInt(mid));
    }

    public String playedCharacter(int m){
        //type = directed, wrote, produced
        String s="INSERT INTO PLAYEDCHARACTER\n";
        s+="(PID,MedID,Part)\n";
        s+="values\n";
        int p=rand.nextInt(pid);
        String c = getUniqueString();
        while(tuples.contains("playedCharacter("+p+","+m+","+c+")")){
            p=rand.nextInt(pid);
        }
        tuples.add("playedCharacter("+p+","+m+","+c+")");
        tuples.add("acted("+p+","+m+")");
        s+="("+p+","+m+",'"+c+"');\n";
        sql+=s;
        return s;
    }

    public void award(){
        for(int i=0; i<awards.length; i++){
            String s="INSERT INTO AWARD\n";
            s+="(Name,Description)\n";
            s+="values\n";
            s+="('"+awards[i]+"','"+getAwardDescription(awards[i])+"');\n";
            sql+=s;
        }
    }

    public String won(int numMovies){
        String s="INSERT INTO WON\n";
        s+="(MedID, AwardName, YearWon, PID)\n";
        s+="values\n";
        String tuple=null, awdname="",p="NULL";
        int m=-1, pint=-1, year=-1;
        while(tuple==null || tuples.contains(tuple)){
            m = rand.nextInt(mid);
            while(media.get(new Integer(m))==null){
                m = rand.nextInt(mid);
            }
            year = media.get(m).intValue();
            awdname = getAward();
            tuple="award("+awdname+","+year+")";
        }
        tuples.add(tuple);
        //String awards[]={"Best Picture", "Best Acting", "Best Writing", "Best Directing"};
        if(awdname.equals(awards[1])){
            while(!tuples.contains("acted("+pint+","+m+")")){
                pint = rand.nextInt(pid);
            }
            p=""+pint;
        }
        else if(awdname.equals(awards[2])){
            while(!tuples.contains("wrote("+pint+","+m+")")){
                pint = rand.nextInt(pid);
            }
            p=""+pint;
        }
        else if(awdname.equals(awards[3])){
            while(!tuples.contains("directed("+pint+","+m+")")){
                pint = rand.nextInt(pid);
            }
            p=""+pint;
        }
        s+="("+m+",'"+awdname+"',"+year+","+p+");\n";
        sql+=s;
        return s;
    }

    public String getUniqueString(){
        String s="";
        int length = rand.nextInt(15)+5;
        for(int i=0; i<length; i++){
            s+=(char)(rand.nextInt(26)+(int)'a');
        }
        while(usedNames.contains(s)){
            s="";
            for(int i=0; i<length; i++){
                s+=(char)(rand.nextInt(26)+(int)'a');
            }
        }
        usedNames.add(s);
        return s;
    }

    public int getYear(){
        return getYear(1900);
    }

    public int getYear(int min){
        return rand.nextInt(100-(min-1900))+min+1;
    }

    public boolean getBoolean(int prob){
        return rand.nextInt(100)<prob;
    }

    public String getLang(){
        String languages[]={"English","French","Spanish","Italian","German"};
        if(getBoolean(90)){
            return languages[0];
        }
        return languages[rand.nextInt(4)+1];
    }

    public String getNetwork(){
        String networks[]={"FOX","ABC","NBC","SYFY","PBS","A&E","CBS"};
        return networks[rand.nextInt(networks.length)];
    }

    public String getStation(){
        String stations[] ={"NPR","APM","CPR","PRI"};
        return stations[rand.nextInt(stations.length)];
    }

    public String getAward(){
        return awards[rand.nextInt(awards.length)];
    }

    public String getAwardDescription(String a){
        //String awards[]={"Best Picture", "Best Acting", "Best Writing", "Best Directing"};
        if(a.equals(awards[0])){
            return "Award for best picture";
        }
        else if(a.equals(awards[1])){
            return "Award for best acting";
        }
        else if(a.equals(awards[2])){
            return "Award for best writing";
        }
        else if(a.equals(awards[3])){
            return "Award for best directing";
        }
        return "Award for something?";
    }

}